﻿using CaltechSoft.Service.Infrastructure;
using CaltechSoft.Service.Models;
using CaltechSoft.Service.SearchCriteria;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CaltechSoft.Service.ReportService.WorkOrder
{
    public class GetWorkOrderDTODbCommand : ICommand<SearchResult<WorkOrderDTO>>
    {
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private static WorkOrderSearchCriteria _criteria;
        private readonly string _connectionString;

        private const string _sql = @"
              WITH PAGING AS (
                SELECT C.*, CUS.name as customerName, U.LastName as lastName, U.FirstName as firstName, row_number() over (order by {0}) as rownumber
                FROM WorkOrders C 
                INNER JOIN CUSTOMERS CUS ON C.CompanyId = CUS.Id 
                INNER JOIN ASPNETUSERS U ON C.UpdatedBy = U.Id 
            {1})
        ";

        public GetWorkOrderDTODbCommand(WorkOrderSearchCriteria criteria, int pageSize, int pageIndex)
        {
            _pageSize = pageSize;
            _pageIndex = pageIndex;
            _criteria = criteria;
            _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        public SearchResult<WorkOrderDTO> Execute()
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                IList<WorkOrderDTO> items = new List<WorkOrderDTO>();
                int totalCount = 0;
                SqlCommand cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalCount = reader.GetInt32(0);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader));
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new SearchResult<WorkOrderDTO>(items, totalCount);
            }
        }
        private WorkOrderDTO GetDto(SqlDataReader reader)
        {
            return new WorkOrderDTO
            {
                WorkOrderId = (int)reader["WorkOrderId"],
                WorkOrderNo = reader["WorkOrderNo"].ToString(),
                PONumber = reader["PONumber"] != DBNull.Value ? reader["PONumber"].ToString() : string.Empty,
                CalibrationCycle = reader["CalibrationCycle"] != DBNull.Value ? reader["CalibrationCycle"].ToString() : string.Empty,
                CalibrationDate = Convert.ToDateTime(reader["CalibrationDate"]),
                NextDueDate = Convert.ToDateTime(reader["NextDueDate"]),
                CustomerName = reader["customerName"].ToString(),
                SalesmanName = string.Format("{0} {1}", reader["firstName"], reader["lastName"])
            };
        }

        private string BuildQuery()
        {
            var query = new StringBuilder();
            var sqlQuery = string.Format(_sql, BuildOrderClause(), BuildWhereClause());
            query.AppendFormat("{0} {1};{0} {2}", sqlQuery, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

        private string BuildWhereClause()
        {
            string whereClause = string.Empty;

            if (_criteria.CustomerId != null)
            {
                whereClause = String.Format(" CUS.ID = {0}", _criteria.CustomerId);

            }
            else if (!string.IsNullOrEmpty(_criteria.CustomerName))
                whereClause = String.Format("AND CUS.Name LIKE '%{0}%'", _criteria.CustomerName.Trim());

            if (whereClause.Trim().StartsWith("AND", StringComparison.InvariantCultureIgnoreCase))
                whereClause = whereClause.Trim().Remove(0, 3);

            if (!String.IsNullOrEmpty(whereClause))
                return " WHERE " + whereClause;
            return string.Empty;
        }

        private static string BuildOrderClause()
        {
            StringBuilder orderClause = new StringBuilder();
            foreach (SearchSortItem sortItem in _criteria.SortItems)
            {
                orderClause.AppendFormat("i.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                orderClause.Append(',');
            }

            if (orderClause.Length <= 0)
            {
                orderClause.Append("C.CreatedDate DESC");
            }
            else
            {
                orderClause.Remove(orderClause.Length - 1, 1);
            }
            return orderClause.ToString();
        }
    }
}
